﻿if exists (select 1
          from sysobjects
          where  id = object_id('bd_fn_getSettle')
          and type in ('IF', 'FN', 'TF'))
   drop function bd_fn_getSettle
go


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-23 17:01:44
-- Database:    [scm_main-hysj2]
-- Description: 系统设置-根据日期获取结算年月，返回年月两个字段表
-- =============================================

CREATE FUNCTION bd_fn_getSettle
(
  @now DATE
)
RETURNS @result TABLE
(
  settleYear INT,
  settleMonth INT
)
AS
BEGIN

  DECLARE @curYear INT
         ,@curMonth INT
--         ,@now DATE

--  SET @now = '2020-07-23'
  SELECT @curYear = YEAR(@now), @curMonth = MONTH(@now)

  DECLARE @settleDayEnable INT
         ,@settleDayBegin INT
         ,@settleDayEnd INT


  SELECT TOP 1 @settleDayEnable = settle_day_enable, @settleDayBegin = settle_day_begin, @settleDayEnd = settle_day_end
  FROM bd_sys_global_params
  --  WHERE dept_id = 203113

  --结算月开始年月
  DECLARE @settleYearBegin  INT, @settleMonthBegin INT
  SET @settleYearBegin = IIF(@curMonth - 1 < 1, @curYear - 1, @curYear)
  SET @settleMonthBegin = IIF(@curMonth - 1 < 1, 12, @curMonth - 1)

  --结算月结束年月
  DECLARE @settleYearEnd INT = YEAR(@now), @settleMonthEnd INT = MONTH(@now)

  DECLARE @settleDateBegin DATE
         ,@settleDateEnd DATE

  --结算开始日期
  SET @settleDateBegin = CAST(@settleYearBegin AS VARCHAR(4)) + '-' + CAST(@settleMonthBegin AS VARCHAR(2)) + '-' + CAST(@settleDayBegin AS VARCHAR(2))
  --结算结束日期
  SET @settleDateEnd = CAST(@settleYearEnd AS VARCHAR(4)) + '-' + CAST(@settleMonthEnd AS VARCHAR(2)) + '-' + CAST(@settleDayEnd AS VARCHAR(2))


  DECLARE @settleYear INT = YEAR(@settleDateEnd), @settleMonth INT = MONTH(@settleDateEnd)

  IF DATEDIFF(DAY, @now, @settleDateEnd) < 0 BEGIN  
  	--当前日期大于结算结束日期，表示已经到下一个月
    SET @settleMonth = IIF(@settleMonth + 1 > 12, 1, @settleMonth + 1)
    SET @settleYear = IIF(@settleMonth + 1 > 12, @settleYear + 1, @settleYear)
  END

  INSERT INTO @result (settleYear, settleMonth)
  VALUES (@settleYear, @settleMonth);

  RETURN
END
GO
